/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import logic.DataTable;
import logic.Varpool;

/**
 *
 * @author Freshman
 */
public class GetTables {

    private Varpool vap = null;
    private DataTable dTab = null;
    private ArrayList<String> tabNames = new ArrayList<String>();;
    private PreparedStatement ps;
    private ResultSet rs;

    public GetTables(Varpool vap) {
        this.vap = vap;
        getTableNames();
        getData();
    }

    //Alle vorhandenen Tabellen der Datenbank mit ihren Namen angeben
    private void getTableNames() {

        try {
            ps = vap.getCon().prepareStatement(
                    "SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema = '" + vap.getDbname() + "';");
            rs = ps.executeQuery();
            while (rs.next()) {
                tabNames.add(rs.getString("table_name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(GetTables.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException ex) {
                Logger.getLogger(GetTables.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    
    // Datensätze aus der Datenbank extrahieren und in DataTable()'s einfügen
    private void getData() {
        ArrayList<DataTable> tables = new ArrayList();
        Object[] clmHeaders = null;
        Object[][] odata = null;
        for (int it = 0; it < tabNames.size(); it++) {
            try {
                // Datensätze der jeweiligen Tabelle holen
                ps = vap.getCon().prepareStatement("SELECT * FROM " + tabNames.get(it));
                rs = ps.executeQuery();
                ResultSetMetaData rsmd = null;
                int clmCnt = -1;
                try {
                    rsmd = rs.getMetaData();
                    clmCnt = rsmd.getColumnCount();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                if (rsmd == null || clmCnt == -1) {
                    throw new RuntimeException("rsmd is null");
                }
                try {
                    rs.first();
                    rs.last();
                    int rowCnt = rs.getRow();
                    rs.first();
                    // odata - für die Werte aus den Zeilen und Spalten der SQL-Tabellen
                    odata = new Object[rowCnt][clmCnt];
                    // clmHeaders - für die Überschriften der Spalten
                    clmHeaders = new Object[clmCnt];
                    for (int i = 1; i <= clmCnt; i++) {
                        clmHeaders[i - 1] = rsmd.getColumnName(i);
                    }
                    int row = 0;
                    rs.first();
                    for (int i = 1; i <= rowCnt; i++) {
                        for (int j = 1; j <= clmCnt; j++) {
                            //System.out.print(rs.getString(i) + " ");
                            odata[row][j - 1] = rs.getString(j);
                        }
                        row++;
                        rs.next();
                    }
                } catch (SQLException e1) {
                    JOptionPane.showMessageDialog(null, "Fehler beim Beziehen der Tabellendaten: " + e1.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                    System.exit(0);
                }
                //Übergeben der Werte an eine Instanz der Klasse DataTable
                dTab = new DataTable(tabNames.get(it), clmHeaders, odata);
                //Die DataTable Instanz in die ArrayList einfügen
                tables.add(dTab);
            } catch (SQLException ex) {
                Logger.getLogger(GetTables.class.getName()).log(Level.SEVERE, null, ex);
                JOptionPane.showMessageDialog(null, "Fehler beim Beziehen der Tabellendaten: " + ex.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                System.exit(0);
            }
            finally {
                try {
                    rs.close();
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(GetTables.class.getName()).log(Level.SEVERE, null, ex);
                    JOptionPane.showMessageDialog(null, "Fehler beim Beenden des PreparedStatement/ResultSet: " + ex.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                    System.exit(0);
                }
            }
        }
        //Die DataTable-Arraylist in Varpool einfügen
        vap.setDataTable(tables);
    }
}
